package com.byhealth.codeTable.dao.impl;

import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import com.byhealth.codeTable.dao.CodeTableDao;

@Repository
public class CodeTableDaoImpl implements CodeTableDao {

	@Resource(name = "jdbcTemplate")
	JdbcTemplate jdbcTemplate;

	// sql存储
	private Map<String, String> sqlMap = new HashMap<String, String>();

	/**
	 * 加载所有sql
	 * */
	public void getALLSql() {
		String sql = " select * from query_sql ";
		System.out.println("sql--->"+sql);
		List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
		System.out.println(list.size());
		if (list != null && list.size() > 0) {
			for (Map<String, Object> map : list) {
				System.out.println(map.get("sql_no"));
				// 获取sql编号
				String sql_no = (String) map.get("sql_no");
				// 获取sql语句
				String sql_statements = (String) map.get("sql_statements");
				System.out.println(map.get("sql_statements"));
				sqlMap.put(sql_no, sql_statements);
			}
		}
		
		System.out.println(sqlMap.get("common_query"));
		System.out.println(sqlMap.get("common_query_one_condition"));
	}

	/**
	 * 获取SQL
	 * */
	public String getSql(String sql_no, Map<String, String> inputMap) {
		String sql = sqlMap.get(sql_no);
		if(sql != null && !"".equals(sql)){
			if ( inputMap != null && inputMap.size() > 0) {
				Iterator it = inputMap.keySet().iterator();
				while (it.hasNext()) {
					String key = it.next() + "";
					String val = inputMap.get(key);
					sql = sql.replaceAll("#" + key + "#", val);
				}
			}

			// 去除无值条件
			while (countStr(sql, "#") >= 2) {
				int start = sql.indexOf("#");
				int end = sql.substring(sql.indexOf("#") + 1).indexOf("#");
				String temp = sql.substring(start, start + end + 2);
				sql = sql.replaceAll(temp, "");
			}
		}else{
			getALLSql();
			sql = sqlMap.get(sql_no);
			if(sql != null && !"".equals(sql)){
				if ( inputMap != null && inputMap.size() > 0) {
					Iterator it = inputMap.keySet().iterator();
					while (it.hasNext()) {
						String key = it.next() + "";
						String val = inputMap.get(key);
						sql = sql.replaceAll("#" + key + "#", val);
					}
				}

				// 去除无值条件
				while (countStr(sql, "#") >= 2) {
					int start = sql.indexOf("#");
					int end = sql.substring(sql.indexOf("#") + 1).indexOf("#");
					String temp = sql.substring(start, start + end + 2);
					sql = sql.replaceAll(temp, "");
				}
			}
		}
		System.out.println("sql-->"+sql);
		return sql;
	}

	/**
	 * 判断str1中包含str2的个数
	 * 
	 * @param str1
	 * @param str2
	 * @return counter
	 */
	private static int countStr(String str1, String str2) {
		int i = 0;
		while (str1.indexOf(str2) != -1) {
			i++;
			str1 = str1.substring(str1.indexOf(str2) + str2.length());
		}
		return i;
	}

	/**
	 * 从数据库中获取查询结果集，并用list封装返回。
	 * */
	public List<Map<String, Object>> query(String sql_no, String sql) {
		List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
		return list;
	}
	
	/**
	 * 查询数据量
	 * */
	public int queryCount(String sql_no, String sql) {
		int num = jdbcTemplate.queryForObject(sql, Integer.class);
		return num;
	}
	
	/**
	 * 从数据库中获取查询结果集，并用list封装返回。
	 * */
	public Map<String, Object> queryFirst(String sql_no, String sql) {
		List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
		if (list != null && list.size() > 0) {
			return list.get(0);
		} else {
			return null;
		}
	}

	/**
	 * 根据表名,ID删除数据
	 * */
	public Boolean deleteTableByOneCondition(String tableName, String fielName, Object fielValue) {
		String sql = "update " + tableName + " set is_del = 1 where " + fielName + " = ";
		if (fielValue instanceof Integer) {
			sql += " "+(Integer)fielValue+" ";
		}else if(fielValue instanceof String){
			sql += " '"+(String)fielValue+"' ";
		}
		jdbcTemplate.execute(sql);
		return true;
	}
}
